


--dbo.TVOCESJERARQUIA

--dbo.TVOZ

use siclan

if not exists (select 1 from sys.objects where object_id = object_id('VocesConNivel'))
begin

create table VocesConNivel
(
  	[idvoz] [int] NULL,
	[descripcion] [varchar](1000) NULL,
	[principal] [bit] NULL,	
	[activa] bit null,
	[vocesPadre] varchar(max)
) ON [PRIMARY]

end

GO

IF OBJECT_ID (N'dbo.GetVocesPadre', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetVocesPadre;
GO
CREATE FUNCTION dbo.GetVocesPadre (@VozId int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
  
    DECLARE @str varchar(4000)                
	SET @str = (SELECT convert(varchar,idvozp) + ';' FROM TVOCESJERARQUIA where idvozs = @VozId FOR XML PATH(''))
	SET @str = SUBSTRING(@str,1,LEN(@str)-1)
	return (@str)
             
        
END
GO

delete from VocesConNivel

go

insert into VocesConNivel (idvoz, descripcion, principal, activa, vocesPadre)
select 
v.IdVoz,
v.DSVOZ,
PRINCIPAL,
v.ACTIVA,
dbo.GetVocesPadre(v.IdVoz) as vocesPadre
from TVOZ v


--select * from VocesConNivel

